#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection
from web.dao.base_dao import BaseDao
from web.models.stock_transaction_data_all import StockTransactionDataAll

"""
StockTransactionDataAll的dao类
"""


class StockTransactionDataAllDao(BaseDao):
    model_class = StockTransactionDataAll

    def find_date_and_average_close_price_between_date_order_by_date_asc(self, begin_date, end_date):
        """
        查询一段时间内的平均收盘价，并按照日期升序排列
        """

        with connection.cursor() as cursor:
            cursor.execute("select t.date_, avg(t.close_price) from stock_transaction_data_all t "
                           "where t.date_ between to_date(%s,'yyyy-mm-dd') "
                           "and to_date(%s,'yyyy-mm-dd') "
                           "group by t.date_ "
                           "order by t.date_ asc", (begin_date, end_date))
            date_and_average_close_price_tuple = cursor.fetchall()
            return date_and_average_close_price_tuple
            # average_close_price_tuple = [x[1] for x in date_and_average_close_price_tuple]
            # return average_close_price_tuple

    def update_total_market_value_and_circulation_market_value_by_code_and_date(self, code: str,
                                                                                total_market_value: float,
                                                                                circulation_market_value: float,
                                                                                date: str):
        """
        根据code、date，更新total_market_value和circulation_market_value
        """
        with connection.cursor() as cursor:
            cursor.execute("update stock_transaction_data t "
                       "set t.total_market_value=%s, t.circulation_market_value=%s "
                       "where t.code_=%s and t.date_=to_date(%s, 'yyyy-mm-dd')",
                       [total_market_value, circulation_market_value, code, date])

    def update_pe_and_pb_and_ps_and_peg_and_dy_by_code_and_date(self, code, date, pe, pb, ps, peg, dy):
        """
        根据code、date，更新市盈率、市净率、市销率、市盈率相对盈利增长比率和股息率
        """
        with connection.cursor() as cursor:
            cursor.execute("update stock_transaction_data t "
                       "set t.pe=%s, t.pb=%s, t.ps=%s, t.peg=%s, t.dy=%s "
                       "where t.code_=%s and t.date_=to_date(%s, 'yyyy-mm-dd')",
                       [pe, pb, ps, peg, dy, code, date])

    def find_last_stock_transaction_data_all_by_code_and_date(self, code: str, date: str) -> StockTransactionDataAll:
        """
        根据code和date，查询上一个交易日的记录
        """

        with connection.cursor() as cursor:
            cursor.execute("select * from (select * from stock_transaction_data_all t "
                           "where t.code_ = %s and t.date_ < to_date(%s,'yyyy-mm-dd') order by t.date_ desc) "
                           "where rownum<=1", [code, date])
            last_list = cursor.fetchall()
            _tuple = last_list[0]
            stock_transaction_data_all = StockTransactionDataAll()
            stock_transaction_data_all.id_ = _tuple[0]
            stock_transaction_data_all.date_ = _tuple[1]
            stock_transaction_data_all.code_ = _tuple[2]
            stock_transaction_data_all.open_price = _tuple[3]
            stock_transaction_data_all.close_price = _tuple[4]
            stock_transaction_data_all.highest_price = _tuple[5]
            stock_transaction_data_all.lowest_price = _tuple[6]
            stock_transaction_data_all.last_close_price = _tuple[7]
            stock_transaction_data_all.change_amount = _tuple[8]
            stock_transaction_data_all.change_range = _tuple[9]
            stock_transaction_data_all.change_range_ex_right = _tuple[10]
            stock_transaction_data_all.up_down = _tuple[11]
            stock_transaction_data_all.turnover_rate = _tuple[12]
            stock_transaction_data_all.volume = _tuple[13]
            stock_transaction_data_all.turnover = _tuple[14]
            stock_transaction_data_all.total_market_value = _tuple[15]
            stock_transaction_data_all.circulation_market_value = _tuple[16]
            stock_transaction_data_all.transaction_number = _tuple[17]
            stock_transaction_data_all.ma5 = _tuple[18]
            stock_transaction_data_all.ma10 = _tuple[19]
            stock_transaction_data_all.ma20 = _tuple[20]
            stock_transaction_data_all.ma60 = _tuple[21]
            stock_transaction_data_all.ma120 = _tuple[22]
            stock_transaction_data_all.ma250 = _tuple[23]
            stock_transaction_data_all.ema12 = _tuple[24]
            stock_transaction_data_all.ema26 = _tuple[25]
            stock_transaction_data_all.dif = _tuple[26]
            stock_transaction_data_all.dea = _tuple[27]
            stock_transaction_data_all.macd = _tuple[28]
            stock_transaction_data_all.five_day_volatility = _tuple[29]
            stock_transaction_data_all.ten_day_volatility = _tuple[30]
            stock_transaction_data_all.twenty_day_volatility = _tuple[31]
            stock_transaction_data_all.two_hundred_fifty = _tuple[32]
            stock_transaction_data_all.rsv = _tuple[33]
            stock_transaction_data_all.k = _tuple[34]
            stock_transaction_data_all.d = _tuple[35]
            stock_transaction_data_all.ha_open_price = _tuple[36]
            stock_transaction_data_all.ha_close_price = _tuple[37]
            stock_transaction_data_all.ha_highest_price = _tuple[38]
            stock_transaction_data_all.ha_lowest_price = _tuple[39]
            stock_transaction_data_all.up = _tuple[40]
            stock_transaction_data_all.mb = _tuple[41]
            stock_transaction_data_all.dn = _tuple[42]
            stock_transaction_data_all.bias5 = _tuple[43]
            stock_transaction_data_all.bias10 = _tuple[44]
            stock_transaction_data_all.bias20 = _tuple[45]
            stock_transaction_data_all.bias60 = _tuple[46]
            stock_transaction_data_all.bias120 = _tuple[47]
            stock_transaction_data_all.bias250 = _tuple[48]
            stock_transaction_data_all.variance5 = _tuple[49]
            stock_transaction_data_all.variance10 = _tuple[50]
            stock_transaction_data_all.variance20 = _tuple[51]
            stock_transaction_data_all.variance60 = _tuple[52]
            stock_transaction_data_all.variance120 = _tuple[53]
            stock_transaction_data_all.variance250 = _tuple[54]
            stock_transaction_data_all.volume_ma5 = _tuple[55]
            stock_transaction_data_all.volume_ma10 = _tuple[56]
            stock_transaction_data_all.volume_ma20 = _tuple[57]
            stock_transaction_data_all.volume_ma60 = _tuple[58]
            stock_transaction_data_all.volume_ma120 = _tuple[59]
            stock_transaction_data_all.volume_ma250 = _tuple[60]
            stock_transaction_data_all.turnover_ma5 = _tuple[61]
            stock_transaction_data_all.turnover_ma10 = _tuple[62]
            stock_transaction_data_all.turnover_ma20 = _tuple[63]
            stock_transaction_data_all.turnover_ma60 = _tuple[64]
            stock_transaction_data_all.turnover_ma120 = _tuple[65]
            stock_transaction_data_all.turnover_ma250 = _tuple[66]
            stock_transaction_data_all.pe = _tuple[67]
            stock_transaction_data_all.pb = _tuple[68]
            stock_transaction_data_all.ps = _tuple[69]
            stock_transaction_data_all.peg = _tuple[70]
            stock_transaction_data_all.dy = _tuple[71]
            return stock_transaction_data_all